package com.sf.osms.dep.processor.parser.excel;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.sf.osms.dep.domain.Agent;
import com.sf.osms.dep.domain.AgentFileMap;
import com.sf.osms.dep.domain.Handover;
import com.sf.osms.dep.domain.Router;
import com.sf.osms.dep.processor.exception.RouterException;
import com.sf.osms.dep.processor.model.EventModel;
import com.sf.osms.dep.processor.parser.EventParser;
import com.sf.osms.dep.processor.util.ExceptionUtils;
import com.sf.osms.dep.processor.util.HandoverLine;
import com.sf.osms.dep.processor.util.RouterConstants;

public class ExcelEventParser implements EventParser<String[],Router>{
	
	private static Logger logger = Logger.getLogger(ExcelEventParser.class);
	@Override
	public EventModel<String[], Router> parse(File file, Agent agentInfo)
			throws RouterException {
		if(null == file || !file.isFile()){
			return null;
		}
		
		EventModel<String[],Router> eventModel = new EventModel<String[],Router>();
		eventModel.setRecordList(new ArrayList<String[]>());
		
		AgentFileMap parseRules = agentInfo.getAgentFileMap();
		if(null == parseRules){
			throw new RouterException("没有符合条件的映射文件"); //FIXME:
		}
		
		//得到文件数据的启始行,默认为第二行为数据启始行
		Integer dataLine = parseRules.getDataLine() == null ? RouterConstants.AGENT_FILE_DATA_LINE_NUM :parseRules.getDataLine();
	
		Integer totalLine = 0;
		 try {
			Workbook wb = WorkbookFactory.create(new FileInputStream(file));
			logger.info("*******代理：" + agentInfo.getName() +"开始解析"+file.getAbsolutePath()+"******");
			for(int s=0;s<wb.getNumberOfSheets();s++){
				Sheet sheet = wb.getSheetAt(s);
				if(null == sheet){
					continue;
				}
				
				totalLine=totalLine+sheet.getPhysicalNumberOfRows();
				for(int r=dataLine-1;r<sheet.getPhysicalNumberOfRows();r++){
					Row row = sheet.getRow(r);
					if(null == row){
						continue;
					}
					
					String[] rowData = new String[row.getLastCellNum()];
					for(int c=0;c<row.getLastCellNum();c++){
						Cell cell = row.getCell(c);
						if(null == cell){
							continue;
						}
						String cellVal = getCellValue(cell);
						
						rowData[c] = cellVal;
						
						
					}
					StringBuilder sb = new StringBuilder();
					for (String column : rowData) {
						sb.append("  "+column);
					}
					logger.info("*******代理：" + agentInfo.getName() + "解析"+file.getAbsolutePath()+"的第"+(r+1)+"行,"+"解析后的数据为:"+sb.toString());
					eventModel.getRecordList().add(removeDoubleQuotes(rowData));
				}
			}
			
			
		}catch (Exception e) {
			e.printStackTrace();
			throw new RouterException(ExceptionUtils.getExceptionStackString(e));
		}
		eventModel.setParsingLineNum(totalLine);
		eventModel.setFile(file);
		return eventModel;
	}
	
	 /**
	  * 解析数据中的双引号
	 */
	private String[] removeDoubleQuotes(String[] values){
		if(values == null || values.length == 0){
			return values;
		}
		String[] newValues = new String[values.length];
		for(int i=0;i<values.length;i++){
			if(StringUtils.isNotEmpty(values[i])){
				newValues[i] = values[i].replaceAll("\"", " ").trim();
			}else{
				newValues[i] = values[i];
			}
		}
		return newValues;
	}
	
	/**
	 * 解析数据中的双引号
	 */
	private HandoverLine removeDoubleQuotes(String[] values,Integer rowNum){
		if(values == null || values.length == 0){
			return new HandoverLine(rowNum,new String[]{});
		}
		String[] newValues = new String[values.length];
		for(int i=0;i<values.length;i++){
			newValues[i] = values[i]==null?null:values[i].replaceAll("\"", " ").trim();
		}
		return new HandoverLine(rowNum,newValues);
	}
	
	/**
	 * 得到正确的单元格中文本
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell) {  
        String cellValue = "";  
          
        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            cellValue = cell.getRichStringCellValue().getString().trim();  
            break;  
        case Cell.CELL_TYPE_NUMERIC:
            cellValue = getNumericStr(cell);  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
            cellValue = cell.getCellFormula();  
            break;  
        default:  
            cellValue = cell.toString();  
        }  
        return cellValue;  
    }  
	
	
	private String getNumericStr(Cell cell){
		short format = cell.getCellStyle().getDataFormat();  
	    SimpleDateFormat sdf = null;  
	    if(format == 14 || format == 31 || format == 57 || format == 58){  
	        //日期  
	        sdf = new SimpleDateFormat("yyyy-MM-dd"); 
	        
	    }else if (format == 20 || format == 32) {  
	        //时间  
	        sdf = new SimpleDateFormat("HH:mm");  
	    }  
	    
	    if(null != sdf){
	    	 double value = cell.getNumericCellValue();  
	    	 Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
	    	 return sdf.format(date); 
	    }
	    
		DecimalFormat df = new DecimalFormat("#");
		return df.format(cell.getNumericCellValue()).toString();
	}
	
	@Override
	public EventModel<HandoverLine, Handover> parseHandovers(File file, Agent agentInfo)
			throws RouterException {
		if(null == file || !file.isFile()){
			return null;
		}
		
		EventModel<HandoverLine,Handover> eventModel = new EventModel<HandoverLine,Handover>();
		eventModel.setRecordList(new ArrayList<HandoverLine>());
		
		AgentFileMap parseRules = agentInfo.getAgentFileMap();
		if(null == parseRules){
			throw new RouterException("没有符合条件的映射文件"); //FIXME:
		}
		
		//得到文件数据的启始行,默认为第二行为数据启始行
		Integer dataLine = parseRules.getDataLine() == null ? RouterConstants.AGENT_FILE_DATA_LINE_NUM :parseRules.getDataLine();
	
		Integer totalLine = 0;
		 try {
			Workbook wb = WorkbookFactory.create(new FileInputStream(file));
			logger.info("*******代理：" + agentInfo.getName() +"开始解析"+file.getAbsolutePath()+"******");
			HandoverLine handoverLine;
			for(int s=0;s<wb.getNumberOfSheets();s++){
				Sheet sheet = wb.getSheetAt(s);
				if(null == sheet){
					continue;
				}
				
				totalLine=totalLine+sheet.getPhysicalNumberOfRows();
				for(int r=dataLine-1;r<=sheet.getLastRowNum();r++){
					Row row = sheet.getRow(r);
					if(null == row ||row.getLastCellNum()<0){
						handoverLine =new HandoverLine(r+1,new String[]{});
						eventModel.getRecordList().add(handoverLine);
						continue;
					}
					
					String[] rowData = new String[row.getLastCellNum()];
					for(int c=0;c<row.getLastCellNum();c++){
						Cell cell = row.getCell(c);
						if(null == cell){
							continue;
						}
						
						//读取内容设置为字符串，免除数字类型的多余小数位
						cell.setCellType(Cell.CELL_TYPE_STRING);
						//将换行符替换掉
						String cellVal = StringUtils.isEmpty(cell.toString())?cell.toString():com.sf.osms.dep.processor.util.StringUtil.StringFilter(cell.toString(), "\r|\n");
						rowData[c] = cellVal;
					}
					StringBuilder sb = new StringBuilder();
					for (String column : rowData) {
						sb.append("  "+column);
					}
					logger.info("*******代理：" + agentInfo.getName() + "解析"+file.getAbsolutePath()+"的第"+(r+1)+"行,"+"解析后的数据为:"+sb.toString());
					eventModel.getRecordList().add(removeDoubleQuotes(rowData,r+1));
				}
			}
			
			
		}catch (Exception e) {
			logger.error(e);
			throw new RouterException("parse Excel file occur exception!");
		}
		eventModel.setParsingLineNum(eventModel.getRecordList().size());
		eventModel.setFile(file);
		return eventModel;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
